package org.liulinlin.sejw.box.alloc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.text.ParseException;

import org.osoa.sca.annotations.Init;
import org.osoa.sca.annotations.Destroy;

import org.liulinlin.sejw.box.BAF2SN;
import org.liulinlin.sejw.db.*;

/**
 * 继承数据访问服务，实现OrderPlanImportService，替代OrderPlanImportServiceImpl。
 * 对外提供添加，查询，判存舱位预分配计划的功能。
 * @author kodylau
 *
 */
public class OrderPlanImportServiceImpl implements OrderPlanImportService {	
	private DBConnectionManager connMgr;
	private BAF2SN baf2SN;
	
	@Init
	public void init() {
		connMgr = DBConnectionManager.getInstance();
		baf2SN = BAF2SN.getInstance();
	}
	
	@Destroy
	public void destroy() {
		connMgr.release();
	}
	
	/**
	 * 添加舱位预分配计划，以从表单提交过来的数据为参数
	 */
	public String post(String id, String scd, String start, String end, 
			String cancel, int total, int big, int gp20, int gp40, int hq40) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		//若该记录已经存在，返回false表示添加记录不成功
		if (exist(id, scd, start, end, cancel, total, big, gp20, gp40, hq40)) {
			System.out.println("Data already exists!");
			return "该预分配计划已经存在！";
		} else {
			DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
			
			try {
				//将String转换为java.sql.Date以便插入数据库
				java.util.Date startDate = df.parse(start);
				java.sql.Date startSQLDate = new java.sql.Date(startDate.getTime());
				java.util.Date endDate = df.parse(end);
				java.sql.Date endSQLDate = new java.sql.Date(endDate.getTime());
				java.util.Date cancelDate = df.parse(cancel);
				java.sql.Date cancelSQLDate = new java.sql.Date(cancelDate.getTime());
				
				Statement stmt = con.createStatement();
				//判断航次是否存在
				ResultSet rs = stmt.executeQuery("select count(*) from ShipSchedule where Scd_ID = '" + scd + "'");
				rs.next();
				if (rs.getInt(1) <= 0) {
					return "不存在航次" + scd;
				}
				
				//先插入受限信息
				stmt.executeUpdate("insert into BoxConstraints values (null, '" + gp20 + "', '" + gp40 + "', '" + hq40 + "')");
				rs = stmt.executeQuery("select count(*) from BoxConstraints");
				int BC_ID = -1;
				try {
					rs.next();
					BC_ID = rs.getInt(1);
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					//shutdown();
				}
				
				String sql = "insert into OrderPlan values ('" +
					id + "', '" +
					scd + "', '" +
					startSQLDate + "', '" +
					endSQLDate + "', '" +
					cancelSQLDate + "', '" +
					total + "', '" +
					big + "', '" +
					BC_ID + "')";
				
				stmt.executeUpdate(sql);
				
				rs.close();
				stmt.close();
			} catch (ParseException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		connMgr.freeConnection("shipping_export", con);
		return "导入成功！";
	}
	/**
	 * 判定是否存在指定的舱位预分配计划
	 * 确保各数据项都有意义
	 * @param orderPlan
	 * @return
	 */
	public boolean exist(String id, String scd, String start, String end, 
			String cancel, int total, int big, int gp20, int gp40, int hq40) {
		//确保传进来的参数合法
		//assert orderPlan.valid() == true;
		//此处先不进行参数合法性检查，待以后添加
		boolean res = false;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return true;
		}
		
		DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		
		try {
			java.util.Date startDate = df.parse(start);
			java.sql.Date startSQLDate = new java.sql.Date(startDate.getTime());
			java.util.Date endDate = df.parse(end);
			java.sql.Date endSQLDate = new java.sql.Date(endDate.getTime());
			java.util.Date cancelDate = df.parse(cancel);
			java.sql.Date cancelSQLDate = new java.sql.Date(cancelDate.getTime());
			
			try {
				Statement stmtBC = con.createStatement();			
				ResultSet rsBC = stmtBC.executeQuery("select * from BoxConstraints where BC_20GP = '" + gp20 + "' and BC_40GP = '" + gp40 + "' and BC_40HQ = '" + hq40 + "'");
			
				while (rsBC.next()) {
					String sql = "select count(*) from OrderPlan " +
					"where OrderPlan_ID = '" + id + "'" +
					" and Scd_ID = '" + scd + "'" +
					" and OrderPlan_StartTime = '" + startSQLDate + "'" +
					" and OrderPlan_EndTime = '" + endSQLDate + "'" +
					" and OrderPlan_CancelTime = '" + cancelSQLDate + "'" +
					" and OrderPlan_TotalBox = '" + total + "'" +
					" and OrderPlan_BigBox = '" + big + "'" +
					" and BC_ID = '" + rsBC.getInt("BC_ID") + "'";
					
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(sql);
					try {
						rs.next();
						if (rs.getInt(1) > 0) {
							res = true;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						//shutdown();
					}
					
					rs.close();
					stmt.close();
				}
				
				rsBC.close();
				stmtBC.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				//shutdown();
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	/**
	 * 根据查询条件，以字符串数组的形式返回查询结果，供web前端显示
	 */
	public List<OrderPlan> query(String id, String scd, String start, String end, 
			String cancel, int total, int big, int gp20, int gp40, int hq40) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		List<OrderPlan> resArrayList = new ArrayList<OrderPlan>();
		
		DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		
		ResultSet rsBC = null;
		Statement stmtBC = null;
		try {
			stmtBC = con.createStatement();
			rsBC = stmtBC.executeQuery("select * from BoxConstraints where BC_20GP = '" + gp20 + "' and BC_40GP = '" + gp40 + "' and BC_40HQ = '" + hq40 + "'");
		
			while (rsBC.next()) {
				String sql = "select * from OrderPlan";
				boolean where = false;
				String cond = "";
				if (!id.isEmpty()) {
					where = true;
					cond += "OrderPlan_ID = '" + id + "' and ";
				}
				if (!scd.isEmpty()) {
					where = true;
					cond += "Scd_ID = '" + scd + "' and ";
				}
				if (!start.isEmpty()) {
					where = true;
					java.util.Date startDate = df.parse(start);
					java.sql.Date startSQLDate = new java.sql.Date(startDate.getTime());
					cond += "OrderPlan_StartTime = '" + startSQLDate + "' and ";
				}
				if (!end.isEmpty()) {
					where = true;
					java.util.Date endDate = df.parse(end);
					java.sql.Date endSQLDate= new java.sql.Date(endDate.getTime());
					cond += "OrderPlan_EndTime = '" + endSQLDate + "' and ";
				}
				if (!cancel.isEmpty()) {
					where = true;
					java.util.Date cancelDate = df.parse(cancel);
					java.sql.Date cancelSQLDate = new java.sql.Date(cancelDate.getTime());
					cond += "OrderPlan_CancelTime = '" + cancelSQLDate + "' and ";
				}
				if (total > 0) {
					where = true;
					cond += "OrderPlan_TotalBox = '" + total + "' and ";
				}
				if (big > 0) {
					where = true;
					cond += "OrderPlan_BigBox = '" + big + "' and ";
				}
				if (true) {
					where = true;
					cond += "BC_ID = '" + rsBC.getInt("BC_ID") + "' and ";
				}
				if (where == true) {
					sql += " where " + cond.substring(0, cond.lastIndexOf("and") - 1);
				}
			
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					OrderPlan item = new OrderPlan(rs.getString("OrderPlan_ID"));
					resArrayList.add(item);
				}
				rs.close();
			}
			rsBC.close();
			stmtBC.close();
		} catch (ParseException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		
		connMgr.freeConnection("shipping_export", con);
		return resArrayList;
	}
	public OrderPlan getOrderPlan(String id) {
		return new OrderPlan(id);
	}
	public void setMakeBoxAllocFormParams(String orderPlanID) {
		baf2SN.setOrderPlanId(orderPlanID);
	}
	public String getOrderPlanID() {
		return baf2SN.getOrderPlanID();
	}
}
